一条异常诡异的 SQL 引起的性能问题

前言：一条诡异的SQL，从执行第二次开始，执行计划发生变化，导致执行效率极低，最终影响核心业务系统的正常运行。

案情分析

根据客户的陈述，该问题可随时重现，无论换个会话还是换个客户端工具都不会受到影响。即使把共享池 flush 掉，再次执行 SQL 仍然会发生同样的现象。

下面我们就来看看案情现场重现：

1）  一条 SQL 在同一个会话中执行两次，第一次执行时间为10秒，但第二次执行时效率很低，执行时间超过1分钟。下面是 SQL 文本：

SELECT /*bbbbb*/A.C_DOC_NO ASC_PLY_APP_NO,

       A.C_PLY_NO AS C_PLY_NO,

       B.N_PRM AS N_PRM,

NVL(TO_CHAR(A.T_APP_TM,'YYYY-MM-DD HH24:MI:SS'), CHR(0)) AS T_APP_TM,

       A.C_BLG_DPT_CDE AS C_DPT_CDE,

       A.C_PROD_NO AS C_PROD_NO,

NVL(B.C_APP_NME, CHR(0)) ASC_APP_NME,

NVL(B.C_APP_TEL, CHR(0)) ASC_APP_TEL

 FROM T_PLY_UNDRMSG A, T_PLY_BASE B, T_FIN_PLYEDR_COLDUE C

 WHERE 1 = 1

  AND ROWNUM < 1000

  AND A.C_DOC_NO = B.C_PLY_APP_NO

  AND A.C_DOC_NO = C.C_PLY_APP_NO(+)

  AND A.C_SOURCE = '1'

  AND A.C_SEND_MRK NOT IN ('2')

  AND DECODE(TRIM(C.C_OPT_NO), CHR(0), NULL, TRIM(C.C_OPT_NO)) IS NULL

  AND (NVL(C.C_PRM_TYP, CHR(0)) IN(CHR(0), 'R1'))

  AND (NVL(C.C_ARP_FLAG, CHR(0)) IN (CHR(0), '0', '3', '4'))

  AND (NVL(C.N_TMS, 0) IN (0, 1))

  AND B.C_HAND_PER = '1012337'

  AND A.T_APP_TM BETWEEN

       TO_DATE('2015-09-29 00:00:00','YYYY-MM-DD HH24:MI:SS') AND

       TO_DATE('2015-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS')

  AND A.T_INPUT_TM BETWEEN

       TO_DATE('2015-09-29 00:00:00','YYYY-MM-DD HH24:MI:SS') AND

       TO_DATE('2015-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS')

2）观察该 SQL 执行计划信息，发现第二次执行计划发生了变化，其中 T_PLY_BASE 表的索引扫描变成了分区表扫描，而且驱动表和被驱动表也发生了改变，第二次执行计划中的 COST 也是在这里出现了明显增高。

第一次执行计划如下：

第二次执行计划：

很明显，的确是第二个执行计划出现了问题，导致了性能的严重下降。但是问题是，为什么同一个 SQL 第二次执行时执行计划会变呢？甚至同一个 SQL 连续两次执行也是如此？

3）  理论上同一个会话上执行的同一个 SQL，第二次执行为软解析（或者软软解析），此时数据库应该重用执行计划，而不是产生新的执行计划。

在 11G 上出现了一些新特性，而其中一个典型会导致 SQL 执行计划发生改变的场景就是 ACS—自适应游标（adaptive_cursor_sharing）。但 ASC 典型出现的场景应该是使用了绑定变量的 SQL，但该 SQL 并未使用绑定，数据库中也并未通过 corsor_sharing 参数强制绑定，看起来应该不是 ACS，那么是什么原因呢？

通过查看资料我们发现，11G 不但出现了ACS这样的自动优化新特性，还出现了另一个自动优化特性—Cardinality Feedback 基数反馈。而通过执行计划中的信息与基数反馈特性的对比，基本可以推断该问题是由11g 新特性统计信息 feedback 导致的 bug，只需要关闭该特性再做验证即可确认。

4）  参考 MOS Statistics (Cardinality) Feedback - Frequently Asked Questions (文档 ID 1344937.1)，文档有对 11GR2 Statistics Feedback 新功能引起执行计划变化的描述、如何确认及解决方法。

解决方案

在全民升级 11G 的这个时代，很多客户都面临着同样的问题：升？或不升？

10g 已经明确告知了官方支持已经到期，因此升级到 11g 新版本的建议已经是众所周知了。然而，仍然有很多用户还在犹豫，其根源就在于他们不能确定在升级到 11g 之后，是否业务运行会遇到这样那样的致命故障，谁也不愿意拿自己的核心系统来冒险。

其实不愿升级的根源在于对新版本新特性的不熟识，既然如此，如果有了成熟版本的最佳实践，能够让你的系统在升级的同时规避这些新特性带来的风险，那还有什么可担心的呢。

本次问题就是典型 11g 新特性——统计信息 Feedback 导致的 bug，这样的问题相对比较常见，我们一般推荐关闭自适应游标共享和统计信息回馈（事实上我们已经总结了不少应该关闭或者调整的新特性），通过两个参数就可以动态关闭它们，在我们很多其他客户的核心库中均已进行过设置，不会对系统造成损害，建议大多数 11g 核心系统最好关闭。

在执行了以下处理后，SQL 执行不但恢复正常，而且运行效率进一步得到了提高。

1）  关闭自适应游标共享和统计信息回馈11g 新特性。

alter system set"_optimizer_use_feedback" = false scope = both;

alter system set"_optimizer_adaptive_cursor_sharing" = false scope = both;

2）同时我们也对这条 SQL 的执行计划进行了优化，建议在表 T_UND_RMSG的T_INPUT_TM 列上的创建单列索引，这样就避免了出现跳扫的执行计划（同时还可将该 SQL 执行计划强制失效）。或者通过comment命令使相关 SQL 强制重新解析（注意，这两种方法都会将该表的所有 SQL 执行计划全部过期失效，代价较高）。

Create index xxxx on T_UND_RMSG(T_INPUT_TM);

Comment on table T_UND_RMSG is ‘xxxx’;

当然，在11g上有一个更为推荐的方法 - DBMS_SHARED_POOL.PURGE，这种方法将只失效特定执行计划异常的子游标，下面给出个参考样例：

select address, hash_value from v$sqlarea where sql_id = 'a6aqkm30u7p90';

ADDRESS          HASH_VALUE

---------------- ----------

C000000EB7ED3420 3248739616

exec dbms_shared_pool.purge('C000000EB7ED3420,3248739616','C');

附件：【11g 新特性】Cardinality Feedback 基数反馈介绍

Cardinality Feedback 基数反馈是版本11.2中引入的关于 SQL 自动性能优化的新特性，该特性用于针对统计信息陈旧、缺失直方图或虽然有直方图但 Cardinality 基数计算不准确的情况，它的出现主要是为了帮助 ORACLE 优化器依据更精准的 Cardinality 产生出更加正确的执行计划。当然，这也意味着 Cardinality 基数的评估准确与否，对于优化器异常重要，直接影响到后续的 JOIN COST 等重要的成本计算评估，从而影响 CBO 下执行计划的选择。

该新特性对于仅执行一次的 SQL 无效，主要用于多次执行的 SQL 执行计划校正。同时也要注意，sys 用户被默认禁用该特性。

在 SQL 第一次执行时，记录存储实际的 Cardinality 和评估的 Cardinality 之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的 Cardinality 重新决策生成执行计划。但是需要注意的是，当使用更准确的 Cardinality 重新生成执行计划时，生成的执行计划有可能与第一次时完全相同。 这个技术的出现是由于优化器在一些情况下不能很好的计算 Cardinality 的数值，比如：统计信息缺失或陈旧、多谓词、直方图缺失或者缺少直方图等等，在这些情况下，Cardinality Feedback 可能会帮上忙。

下面来看看 Cardinality Feedback 是如何发挥作用的（使用普通用户测试）：

在 order_items 表上有2个谓词：o.unit_price= 15 and quantity > 1，由于优化器对于联合谓词评估不准，导致 ORACLE 优化器认为经过2个联合谓词的过滤，order_items 表只返回一条记录，进而优化器选择了 nest loop 的执行计划：

如果 Cardinality Feedback 被开启，在 SQL 第一次执行结束后，ORACLE 会把实际的 Cardinality 与评估的 Cardinality 做比较，如果差异较大，这些实际的 Cardinality 会被存储以期待 SQL 再次被执行时这些 Cardinality 被优化器所使用。

来看看第二次执行的情况：

很明显执行计划发生了改变，CBO 依据第一次执行所记录的 Cardinality，重新评估执行计划，在第二次执行时，已经选择了 HASH JOIN 的执行计划，在执行计划的 NOTE 部分也看到了 cardinality feedback used for thisstatement 字样。

11GR2 中可以通过 V$SQL_SHARED_CURSOR 视图的 USE_FEEDBACK_STATS 字段来表示该 SQL 是否使用了 Cardinality Feedback 特性。

通过 10053 trace 两次执行过程，可以很容易发现 Cardinality Feedback 是如何发挥作用的。例如：

很明显可以看到在 E-Rows 列中，SQL 第一次运行时，由于未收集 T1 表上的统计信息且关闭了动态采样（optimizer_dynamic_sampling=0），所以基数评估值和实际值有着较大的差距。

cardinality feedback used for this statement 说明第二次执行时使用了 Cardinality Feedback 基数反馈，且其基数评估也十分精确了，这是因为第二次执行时考虑到第一次执行时的基数反馈。

我们换个角度，用10053来看看更详细的 cardinality feedback：

可以看到第二次执行时 SQL 最终转换加入了 OPT_ESTIMATE (TABLE"T1" ROWS=1920.000000 )的 HINT ，OPT_ESTIMATE HINT 一般由 kestsaFinalRound (内核函数)生成。该 HINT 用以纠正优化器评估，最终反应出优化的不足或者导致 BUG。

可以通过 V$SQL_SHARED_CURSOR 和来找出现有系统 shared pool 中仍存在的使用了 Cardinality Feedback 基数反馈的子游标：

可以使用 cardinality HINT 来强制使用 Cardinality Feedback 。

select /*+ cardinality(t1,  1) */ count(*) from t1;

新特性总是听起来很美，真的好用吗，其实并不见得。Cardinality Feedback 基数反馈在一些场景中也会导致错误的执行计划，所以我们可能会遇到第一次执行很快，第二次反而慢的情况。 cardinality feedback 技术给优化器提供了更加精准的 cardinality 来帮助优化器更容易产生优秀的执行计划，但 ORACLE 里也有其他的一些技术来提升评估 cardinality 的精准性，如：dynamic sampling，多列联合的统计信息收集等，如果合理的采用了这些技术，cardinalityfeedback 技术其实并没有太大的必要性。

• 如何禁用 Cardinality Feedback 基数反馈

考虑到 stable，往往考虑关闭该特性。

可以通过多种方法禁用该特性：

1. 使用 _optimizer_use_feedback 隐藏参数

session 级别

SQL>alter session set “_optimizer_use_feedback”=false;

system级别

SQL>alter system set “_optimizer_use_feedback”=false;

2. 使用opt_param(‘_optimizer_use_feedback’ ‘false’) HINT

select /*+opt_param(‘_optimizer_use_feedback’ ‘false’) cardinality(t1,1) */ count(*)

from t1;

• 已知的典型cardinatilty feedback问题     

Fixed in 11.2.0.2                                  
Note 8608703.8 Bug 8608703 - SubOptimal Execution Plancreated by Cardinality Feedback        
Note 9465425.8 Bug 9465425 - New cursors generated after cardinality feedback                
Note 9342979.8 Bug 9342979 Suboptimal plan change with cardinatilty feedback      
                                                                                                
Fixed in 12c                          
Note 8521689.8 Bug 8521689 - SubOptimal execution plan onsecond execution of GROUP BY query  
Note 8729064.8 Bug 8729064 Adaptive cursor sharing fails to share /USE_FEEDBACK_STATS not set

如何加入"云和恩墨大讲堂"微信群

搜索 盖国强（Eygle）微信号：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。

近期文章